package simorg;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.h2.jdbcx.JdbcDataSource;

public class DatabaseAccess {
	// identifiers for the tables in the database
	protected enum Table {
		CLASSIFICATION_TESTS, DERIVATIVE_TESTS, STRING, UNKNOWNS
	}

	// names for the sheets in the Excel spreadsheet
	protected static final String[] SHEET_NAME = { "Tests", "Derivatives",
			"Strings", "Unknowns" };

	// names for the tables in the database
	protected static final String[] TABLE_NAME = {
			"CLASSIFICATION_TESTS_TABLE", "DERIVATIVE_TESTS_TABLE",
			"STRINGS_TABLE", "UNKNOWNS_TABLE" };

	protected static final int VALUE_NAME = 0;

	protected static final int VALUE_TYPE = 1;

	protected static final int[] NUM_COLUMNS = {

	// Classifications
			7,

			// Derivatives
			7,

			// Strings
			2,

			// Unknowns
			53 };

	// SQL "typedefs"
	protected static final String INT_KEY = "INTEGER PRIMARY KEY";

	protected static final String INTEGER = "INTEGER";

	protected static final String SHORT_STRING = "CHAR(256) NOT NULL";

	protected static final String LONG_STRING = "CHAR(1024) NOT NULL";

	// names and types of values in each table
	protected static final String[][][] VALUES = {

	// Classifications
			{ { "ID", INT_KEY }, { "NAME", SHORT_STRING },
					{ "MINUTES", INTEGER }, { "DESCRIPTION", LONG_STRING },
					{ "DERIVATIVE_ORDER", INTEGER }, { "WEIGHING", INTEGER },
					{ "WEIGHING_DESCRIPTION", INTEGER } },

			// Derivatives
			{ { "ID", INT_KEY }, { "NAME", SHORT_STRING },
					{ "MINUTES", INTEGER }, { "DESCRIPTION", LONG_STRING },
					{ "DERIVATIVE_ORDER", INTEGER }, { "WEIGHING", INTEGER },
					{ "WEIGHING_DESCRIPTION", INTEGER } },

			// Strings
			{ { "STRING_ID", INT_KEY }, { "STRING", LONG_STRING } },

			// Unknowns
			{ { "ID", INT_KEY }, { "COMMON_NAME", SHORT_STRING },
					{ "IUPAC_NAME", SHORT_STRING }, { "CLASS", SHORT_STRING },
					{ "MELTING_POINT", INTEGER }, { "BOILING_POINT", INTEGER },
					{ "USE", INTEGER }, 
					{ "T2", SHORT_STRING },
					{ "T3", SHORT_STRING }, 
					{ "T4", SHORT_STRING },
					{ "T5", SHORT_STRING }, 
					{ "T6", SHORT_STRING },
					{ "T7", SHORT_STRING },
					{ "T8", SHORT_STRING },
					{ "T9", SHORT_STRING },
					{ "T10", SHORT_STRING },
					{ "T11", SHORT_STRING },
					{ "T12", SHORT_STRING },
					{ "T13", SHORT_STRING },
					{ "T14", SHORT_STRING },
					{ "T15", SHORT_STRING },
					{ "T16", SHORT_STRING },
					{ "T17", SHORT_STRING },
					{ "T18", SHORT_STRING },
					{ "T19", SHORT_STRING },
					{ "T20", SHORT_STRING },
					{ "T21", SHORT_STRING },
					{ "T22", SHORT_STRING },
					{ "T23", SHORT_STRING },
					{ "T24", SHORT_STRING },
					{ "T25", SHORT_STRING },
					{ "", "" }, // empty column intentional - DO NOT REMOVE
					{ "D2", SHORT_STRING },
					{ "D3", SHORT_STRING },
					{ "D4", SHORT_STRING },
					{ "D5", SHORT_STRING },
					{ "D6", SHORT_STRING },
					{ "D7", SHORT_STRING },
					{ "D8", SHORT_STRING },
					{ "D9", SHORT_STRING },
					{ "D10", SHORT_STRING },
					{ "D11", SHORT_STRING },
					{ "D12", SHORT_STRING },
					{ "D13", SHORT_STRING },
					{ "D14", SHORT_STRING },
					{ "D15", SHORT_STRING },
					{ "D16", SHORT_STRING },
					{ "D17", SHORT_STRING },
					{ "D18", SHORT_STRING },
					{ "D19", SHORT_STRING },
					{ "D20", SHORT_STRING },
					{ "D21", SHORT_STRING },
					{ "D22", SHORT_STRING }
			// */
			} };
	private static final String URL = "jdbc:h2:~/";

	static private JdbcDataSource m_dataSource = new JdbcDataSource();

	/**
	 * Get the linked data source.
	 * 
	 * @return JdbcDataSource, the data source to which this is linked; null if
	 *         it has not been linked.
	 */
	static protected JdbcDataSource getDataSource() {
		return m_dataSource;
	}

	/**
	 * Link the interface to the SimOrgDatabase.
	 */
	static protected void linkToDatabase() {
		System.out.println("Linking to database");

		// link to the database
		m_dataSource.setURL(URL + "SimOrgDb");
		m_dataSource.setUser("sa");
		m_dataSource.setPassword("");
	}

	public static void main(final String[] a_args) {
		DatabaseAccess.linkToDatabase();

		try {
			DatabaseAccess.makeTable("TEST_TABLE", "");
		} catch (final SQLException ex) {
			ex.printStackTrace();
		}
	}

	/**
	 * Create a table in the database, dropping it if it already exists.
	 * 
	 * @param a_tableName
	 *            The name of the table to create.
	 * @param a_args
	 *            The arguments sent to the table creation (i.e. types of
	 *            values).
	 */
	static protected void makeTable(final String a_tableName,
			final String a_args) throws SQLException {

		// drop the table if it exists
		final String drop = "DROP TABLE IF EXISTS " + a_tableName;
		sqlExecute(drop);

		// create the table
		final String create = "CREATE TABLE " + a_tableName + " " + a_args;
		sqlExecute(create);
	}

	/**
	 * Execute and report an SQL statement to the linked database.
	 * 
	 * @param a_sqlString
	 *            The SQL statement to execute.
	 * @return boolean, value returned by executing the statement (see
	 *         java.sql.Statement.execute )
	 */
	static protected boolean sqlExecute(final String a_sqlString)
			throws SQLException {
		// System.out.println( "Execute: " + a_sqlString );

		return sqlStatement().execute(a_sqlString);
	}

	static protected ResultSet sqlExecuteQuery(final String a_sqlString)
			throws SQLException {
		// System.out.println( "Execute query: " + a_sqlString );

		return sqlStatement().executeQuery(a_sqlString);
	}

	/**
	 * Create a SQL statement for use on the opened connection.
	 * 
	 * @return Statement, the statement created.
	 */
	static private Statement sqlStatement() throws SQLException {
		return getDataSource().getConnection().createStatement();
	}
}
